﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using WebService.VO;

namespace WebService.DAO
{
    public class UserPlacesDAO
    {
         private dbConnection conn;
        /// <summary>
        /// khởi tạo đối tượng xử lý dl
        /// </summary>
        public UserPlacesDAO()
        {
            conn = new dbConnection();
        }

         /// <summary>
         /// get user place by device id
         /// </summary>
         /// <param name="device"></param>
         /// <returns></returns>
        public DataTable getUserPlaces(DeviceVO device, string language)
        {
            if (language.Equals("vi"))
            {
                return conn.GetDataTable("Select a.[ItemID],[ItemName],[CategoryID],[Rate],[Address],[Phone],[Email],[Note],[X],[Y],[Image] From tbl_UserPlaces a, tbl_Items b Where a.ItemID = b.ItemID AND DeviceID=" + device.Id);
            }
            return conn.GetDataTable("Select a.[ItemID],b.[ItemName] as ItemName,[CategoryID],[Rate],b.[Address] as Address,[Phone],[Email],b.[Note] as Note,[X],[Y],[Image] From tbl_Items a, tbl_ItemsTranslation b, tbl_UserPlaces c Where a.ItemID=b.ItemID And a.ItemID = c.ItemID And Language='" + language + "' AND DeviceID=" + device.Id);
        }

         /// <summary>
         /// check item existing in user places
         /// </summary>
         /// <param name="favorite"></param>
         /// <returns></returns>
         public bool check(UserPlaceVO place)
         {
             string sqlSelect = "Select count(*) From tbl_UserPlaces Where DeviceID=@DeviceID and ItemID=@ItemID";
             IDictionary<string, string> d = new Dictionary<string, string>();

             d.Add("@DeviceID", place.Device.Id.ToString());
             d.Add("@ItemID", place.Item.ItemId.ToString());

             int result = conn.ExecuteQuery(sqlSelect, d, false);
             if (result == 0)
                 return false;
             return true;
         }

         /// <summary>
         /// add item to user places
         /// </summary>
         public void insert(UserPlaceVO place)
         {
             string sqlInsert = "INSERT INTO tbl_UserPlaces VALUES(@DeviceID, @ItemID)";

             IDictionary<string, string> d = new Dictionary<string, string>();

             d.Add("@DeviceID", place.Device.Id.ToString());
             d.Add("@ItemID", place.Item.ItemId.ToString());

             conn.ExecuteQuery(sqlInsert, d, false);

         }

         /// <summary>
         /// delete item from user places
         /// </summary>
         public void delete(UserPlaceVO place)
         {
             string sql = "DELETE FROM tbl_UserPlaces WHERE ItemID=@ItemId AND DeviceID=@DeviceId";

             IDictionary<string, string> d = new Dictionary<string, string>();

             d.Add("@ItemId", place.Item.ItemId.ToString());
             d.Add("@DeviceId", place.Device.Id.ToString());


             conn.ExecuteQuery(sql, d, false);
         }
    }
}